package com.admin.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.admin.bean.CompanyBean;
import com.admin.util.SqlUtil;

@Repository("companyDao")
public class CompanyDao {

	@Autowired private JdbcTemplate jdbcTemplate;
	@Autowired private QueryRunner queryRunner;
	
	public int insertCompany(CompanyBean company){
		final String sql = "insert into admincompany(companyName,companyMemo,companyAddr,companyTel,companyFax,companyHome,bussiness,sppercent,spcheck,commandMemo,spstatus)" +
				"values(?,?,?,?,?,?,?,?,?,?,?)";
		try {
			return jdbcTemplate.update(sql, new Object[]{company.getCompanyName(),company.getCompanyMemo(),"","",
					"","",company.getBussiness(),company.getSppercent(),company.getSpcheck(),company.getCommandMemo(),company.getSpstatus()});
		} catch (DataAccessException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public int updateCompany(CompanyBean company){
		final String sql = "update admincompany set companyName=?,companyMemo=?,companyAddr=?,companyTel=?,companyFax=?,companyHome=?,bussiness=?,sppercent=?,spcheck=?,commandMemo=?,spstatus=? where companyId=?";
		try {
			return jdbcTemplate.update(sql, new Object[]{company.getCompanyName(),company.getCompanyMemo(),company.getCompanyAddr(),company.getCompanyTel(),
					company.getCompanyFax(),company.getCompanyHome(),company.getBussiness(),company.getSppercent(),company.getSpcheck(),company.getCommandMemo(),company.getSpstatus(),company.getCompanyId()});
		} catch (DataAccessException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public int delCompany(int companyId){
		final String sql = "delete from admincompany where companyId = ?";
		try {
			return jdbcTemplate.update(sql, new Object[]{companyId});
		} catch (DataAccessException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public CompanyBean findCompanyById(int companyId) throws SQLException {
		final String sql = "select * from admincompany where companyId = ?";
		return queryRunner.query(sql, new BeanHandler<CompanyBean>(CompanyBean.class), new Object[]{companyId});
	}
	
	public List<CompanyBean> findList(int start,int pageSize) throws SQLException{
		final String sql = SqlUtil.getPageSqlForSqlServerAsc("*", "companyId", "admincompany", "companyId", "1=1", start, pageSize);//"select * from company order by companyId desc limit ?,?";
		return queryRunner.query(sql, new BeanListHandler<CompanyBean>(CompanyBean.class),new Object[]{});
	}
	
	public List<CompanyBean> findList(String condition,int start,int pageSize) throws SQLException{
		final String sql = SqlUtil.getPageSqlForSqlServerAsc("*", "companyId", "admincompany", "companyId", condition, start, pageSize);//"select * from company order by companyId desc limit ?,?";
		return queryRunner.query(sql, new BeanListHandler<CompanyBean>(CompanyBean.class),new Object[]{});
	}
	
	public int findCount(){
		final String sql = "select count(0) from admincompany";
		return jdbcTemplate.queryForInt(sql);
	}
	
	public int findCount(String condition){
		final String sql = "select count(0) from admincompany where "+condition;
		return jdbcTemplate.queryForInt(sql);
	}
	
	public List<CompanyBean> findAllList() throws SQLException{
		final String sql = "select * from admincompany order by companyId desc";
		return queryRunner.query(sql, new BeanListHandler<CompanyBean>(CompanyBean.class));
	}
}
